package com.liangtian.mdm.material.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.liangtian.mdm.base.dao.JdbcDao;
import com.liangtian.mdm.base.utils.ShiroSessionUtils;
import com.liangtian.mdm.dm.jsp.common.ExcelUtil;
import com.liangtian.mdm.dm.jsp.util.SqlUtil;
import com.liangtian.mdm.dm.listener.CacheUtil;
import com.liangtian.mdm.material.common.util.ExcelExportUtil;
import com.liangtian.mdm.material.dao.WlLbDao;
import com.liangtian.mdm.material.dao.WlLbmbDao;
import com.liangtian.mdm.material.dao.WlLbmbtzlDao;
import com.liangtian.mdm.material.dao.WlLbmbtzlqzDao;
import com.liangtian.mdm.material.model.WlLb;
import com.liangtian.mdm.material.model.WlLbmb;
import com.liangtian.mdm.material.model.WlLbmbtzl;
import com.liangtian.mdm.material.model.WlLbmbtzlqz;
import com.liangtian.mdm.material.service.WlLbmbService;
import com.liangtian.mdm.system.user.model.SysUser;
import com.liangtian.mdm.system.user.util.ExcelImport;
import com.liangtian.mdm.util.ConstantBean;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class WlLbmbServiceImpl implements WlLbmbService {

    @Autowired
    private WlLbmbDao wlLbmbDao;
    @Autowired
    private WlLbmbtzlDao wlLbmbtzlDao;
    @Autowired
    private WlLbDao wlLbDao;
    @Autowired
    private JdbcDao jdbcDao;
    @Autowired
    private CacheUtil cacheUtil;
    @Autowired
    private SqlUtil sqlUtil;
    @Autowired
    private WlLbmbtzlqzDao wlLbmbtzlqzDao;

    @Autowired
    private DataSource dataSource;

    private ExcelImport excelImport = new ExcelImport();
    // Session中保存的当前登录用户的信息
    //@Value("${ConstantBean.CURRENT_SESSION_USER}")
    //private String ConstantBean.CURRENT_SESSION_USER;

    @SuppressWarnings({"rawtypes", "unchecked"})
    @Override
    public PageInfo getWlLbmbListByCond(Map<String, Object> map) {
        PageInfo<List<WlLbmb>> pageInfo = null;
        try {
            Object pNo = map.get("pageNum");
            int pageNum, pageSize;
            // export
            if (pNo != null && !"NaN".equals(pNo)) {
                pageNum = Integer.parseInt(pNo.toString());// 页码
            } else {
                pageNum = 1;
            }
            // pageSize=0时不分页
            pageSize = Integer.parseInt(map.get("limit").toString());// 模型定义页面要显示数据条数
            // 例如
            // 10,20,50,100
            PageHelper.startPage(pageNum, pageSize);// 开始分页标志（当前页，显示条数）
            List<WlLbmb> list = wlLbmbDao.getWlLbmbListByCond(map);
            pageInfo = new PageInfo(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pageInfo;
    }

    @Override
    public WlLbmb getWlLbmbById(WlLbmb wlmb) {
        return wlLbmbDao.getWlLbmbById(wlmb);
    }

    @Override
    public List<WlLbmb> getAllValidWlLbmbByLbbm(String lbbm) {
        return wlLbmbDao.getAllValidWlLbmbByLbbm(lbbm);
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    @Override
    public PageInfo getWlLbmbListByCondByAdd(Map<String, Object> map) {
        PageInfo<List<WlLbmb>> pageInfo = null;
        try {
            Object pNo = map.get("pageNum");
            int pageNum, pageSize;
            // export
            if (pNo != null && !"NaN".equals(pNo)) {
                pageNum = Integer.parseInt(pNo.toString());// 页码
            } else {
                pageNum = 1;
            }
            // pageSize=0时不分页
            pageSize = Integer.parseInt(map.get("limit").toString());// 模型定义页面要显示数据条数
            // 例如
            // 10,20,50,100
            PageHelper.startPage(pageNum, pageSize);// 开始分页标志（当前页，显示条数）
            List<WlLbmb> list = wlLbmbDao.getWlLbmbListByCondByAdd(map);
            pageInfo = new PageInfo(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pageInfo;
    }

    @Override
    public int getWlLbmbListCountByCond(WlLbmb lbmb) {
        Map<String, Object> map = new HashMap<>();
        map.put("mbname", lbmb.getMbname());
        return wlLbmbDao.getWlLbmbListCountByCond(map);
    }

    @Override
    public int insertWlLbmb(WlLbmb lbmb) {
        return wlLbmbDao.insertWlLbmb(lbmb);
    }

    @Override
    public int getCountWlLbmbByCond(WlLbmb lbmb) {
        Map<String, Object> map = new HashMap<>();
        map.put("mbname", lbmb.getMbname());
        map.put("id", lbmb.getId());
        return wlLbmbDao.getCountWlLbmbByCond(map);
    }

    @Override
    public void updateWlLbmb(WlLbmb lbmb) {
        wlLbmbDao.updateWlLbmb(lbmb);

    }

    @Override
    public String copyWlLbmb(Map<String, Object> map) {
        Connection conn = null;
        CallableStatement stmt = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            String sql = "{call p_wl_lbmb_copy(?,?,?,?,?,?)}";
            stmt = conn.prepareCall(sql);
            stmt.setString(1, map.get("p_mbname").toString());
            stmt.setString(2, map.get("p_userid").toString());
            stmt.setString(3, map.get("p_username").toString());
            stmt.setString(4, map.get("p_wl_lbmb_from").toString());
            stmt.setString(5, map.get("p_lbbm_to").toString());
            stmt.registerOutParameter(6, Types.VARCHAR);
            stmt.execute();
            return stmt.getString(6);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    DataSourceUtils.doReleaseConnection(conn, dataSource);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    @Override
    public PageInfo getWlLbmbById(Map<String, Object> map) {
        PageInfo<List<WlLbmb>> pageInfo = null;
        try {
            Object pNo = map.get("pageNum");
            int pageNum, pageSize;
            // export
            if (pNo != null && !"NaN".equals(pNo)) {
                pageNum = Integer.parseInt(pNo.toString());// 页码
            } else {
                pageNum = 1;
            }
            // pageSize=0时不分页
            pageSize = Integer.parseInt(map.get("limit").toString());// 模型定义页面要显示数据条数
            // 例如
            // 10,20,50,100
            PageHelper.startPage(pageNum, pageSize);// 开始分页标志（当前页，显示条数）
            List<WlLbmb> list = wlLbmbDao.getWlLbmbById(map);
            pageInfo = new PageInfo(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pageInfo;
    }

    @Override
    public void insertWlLbmbUpdateByMap(Map<String, Object> map) {
        wlLbmbDao.insertWlLbmbUpdateByMap(map);

    }

    @Override
    @SuppressWarnings("static-access")
    public Map<String, Object> importWllbmbData(InputStream inputStream) {
        Map<String, Object> result = new HashMap<>();
        boolean success = true;
        String info = null;
        try {
            //读取excel
            Workbook wb = new XSSFWorkbook(inputStream);
            Sheet sheet = wb.getSheetAt(0);
            List<Object[]> valueList = excelImport.read(sheet);
            valueList.remove(0);//去掉表头
            // 插入数据

            //校验数据
            List<WlLbmbtzlqz> list = new ArrayList<WlLbmbtzlqz>();
            String str = getData(valueList, list);
            if (!StringUtils.isEmpty(str)) {
                success = false;
                info = str;
            }
            if (!CollectionUtils.isEmpty(list))
                insert(list);


        } catch (IOException e) {
            success = false;
        } catch (InvalidFormatException e) {
            success = false;
        }
        result.put("success", success);
        result.put("info", info);
        return result;
    }

    private String getData(List<Object[]> valueList, List<WlLbmbtzlqz> list) {
        StringBuffer sb = new StringBuffer();//存放错误信息
        int currentError = 0;
        Map<String, String> tzl_map = new HashMap<String, String>();
        for (int i = 0; i < valueList.size(); i++) {
            boolean flag = true;//标识当前excel行中数据是否正确
            if (currentError >= 20)
                break;

            String errorRowInfo = "第" + (i + 2) + "行";
            String[] obj = (String[]) valueList.get(i);
            //小类代码，
            String xldm = obj[0].trim();
            //模板名称
            String mbmc = obj[2].trim();
            //特征量序号
            String tzlxh = obj[3].trim();
            //特征量名称
            String tzlmc = obj[4].trim();
            //特征量值
            String tzlz = obj[5].trim();
            //特征量说明
            String tzlsm = obj[6].trim();

            Map<String, WlLb> rightWlLb = new HashMap<String, WlLb>();
            Map<String, String> errorWlLb = new HashMap<String, String>();
            WlLbmb wlLbmb = new WlLbmb();

            //小类代码校验开始
            //1 小类代码不能为空
            if (StringUtils.isEmpty(xldm)) {
                sb.append(errorRowInfo + "小类代码：" + xldm + "为空;\r\n");
                currentError++;
                flag = false;
                continue;
            }

            //2 小类代码存在且有效,优先从正确的或者错误的MAP中查询该小类对象，如果都不存在，在去数据库中查询
            WlLb wllb = null;
            String msg = "";
            if (errorWlLb.containsKey(xldm)) {
                msg = errorWlLb.get(xldm);
                sb.append(msg);
                currentError++;
                flag = false;
                continue;
            }

            if (rightWlLb.containsKey(xldm)) {
                wllb = rightWlLb.get(xldm);
            } else {
                WlLb wllb_cond = new WlLb();
                wllb_cond.setLbbm(xldm);
                WlLb wlLb = wlLbDao.getWlLbById(wllb_cond);
                if (wlLb == null) {
                    msg = errorRowInfo + "小类代码：" + xldm + "不存在;\r\n";
                    sb.append(msg);
                    errorWlLb.put(xldm, msg);
                    currentError++;
                    flag = false;
                    continue;
                } else {
                    if ("1".equals(wlLb.getNouse())) {
                        msg = errorRowInfo + "小类代码：" + xldm + "在系统中状态不能为停用;\r\n";
                        sb.append(msg);
                        errorWlLb.put(xldm, msg);
                        currentError++;
                        flag = false;
                        continue;
                    }
                }
                rightWlLb.put(xldm, wllb);
            }
            //模板名称不能为空
            if (StringUtils.isEmpty(mbmc)) {
                sb.append(errorRowInfo + "模板名称：" + mbmc + "为空;\r\n");
                currentError++;
                flag = false;
                continue;
            }
            //特征量序号不能为空
            if (StringUtils.isEmpty(tzlxh)) {
                sb.append(errorRowInfo + "特征量序号：" + tzlxh + "为空;\r\n");
                currentError++;
                flag = false;
                continue;
            }
            //特征量名城不能为空
            if (StringUtils.isEmpty(tzlmc)) {
                sb.append(errorRowInfo + "特征量名称：" + tzlmc + "为空;\r\n");
                currentError++;
                flag = false;
                continue;
            }
            // 1 通过小类代码，模版名称，看模板是否存在
            wlLbmb.setLbbm(xldm);
            wlLbmb.setMbname(mbmc);
            wlLbmb = wlLbmbDao.getWlLbmbByLbbmAndMbmc(wlLbmb);
            WlLbmbtzl wl_tzl = null;
            if (wlLbmb == null) {
                sb.append(errorRowInfo + "模板：" + mbmc + "不存在;\r\n");
                currentError++;
                flag = false;
                continue;
            } else {
                // 2 模板id,特征量编号找到特征量id
                String wl_mb_id = wlLbmb.getId();
                WlLbmbtzl wlLbmbtzl = new WlLbmbtzl();
                wlLbmbtzl.setWl_lbmb_id(wl_mb_id);
                wlLbmbtzl.setXh(Integer.parseInt(tzlxh));
                wl_tzl = wlLbmbtzlDao.getWlLbmbtzlByWlLbmbIdAndTzlxh(wlLbmbtzl);
                if (wl_tzl == null) {
                    sb.append(errorRowInfo + "模板特征量：" + tzlmc + "【" + tzlxh + "】不存在或已删除;\r\n");
                    currentError++;
                    flag = false;
                    continue;
                }
                //校验模板特征量 17-06-30
                if (!tzlmc.equals(wl_tzl.getTzlmc())) {
                    sb.append(errorRowInfo + "模板特征量：" + tzlmc + "不存在;\r\n");
                    currentError++;
                    flag = false;
                    continue;
                }
                //特征量取值排重校验 17-06-29
                WlLbmbtzlqz tzlqz = new WlLbmbtzlqz();
                tzlqz.setQz(tzlz);//特征量取值
                tzlqz.setWl_lbmbtzl_id(wl_tzl.getId());//特征量ID
                int count = wlLbmbtzlqzDao.getWlLbmbtzlqzListCountByCond(tzlqz);
                if (count > 0) {
                    sb.append(errorRowInfo + "特征量取值:" + tzlz + "已存在!\r\n");
                    currentError++;
                    flag = false;
                    continue;
                }
            }

            //校验excel中是否存在重复数据
            if (flag) {
                String tzl_key = xldm + ":" + mbmc + ":" + tzlmc + ":" + tzlz;
                String value = tzl_map.get(tzl_key);
                if (null == value)
                    tzl_map.put(tzl_key, i + 2 + "");
                else {
                    sb.append(errorRowInfo + "与第" + value + "行数据重复!\r\n");
                    currentError++;
                    flag = false;
                }
            }
            if (flag) {
                // 获取session中用户
                SysUser user = ShiroSessionUtils.getSessionSysUser(ConstantBean.CURRENT_SESSION_USER);
                WlLbmbtzlqz wlLbmbtzlqz = new WlLbmbtzlqz();
                String wlLbmbtzl_id = wl_tzl.getId();
                //通过小类代码，模板id，特征量序号找到特征量id，如果id存在，则插入特征量值
                wlLbmbtzlqz.setWl_lbmbtzl_id(wlLbmbtzl_id);
                wlLbmbtzlqz.setQz(tzlz);
                wlLbmbtzlqz.setSm(tzlsm);
                //wlLbmbtzlqz.setCreate_time(new Date());
                wlLbmbtzlqz.setCreator_name(user.getUserName());
                wlLbmbtzlqz.setCreator_id(user.getUserAccount());
                list.add(wlLbmbtzlqz);
            }
        }
        return sb.toString();
    }

    private void insert(List<WlLbmbtzlqz> list) {
        StringBuilder sql = new StringBuilder();
        StringBuilder sqlForm = new StringBuilder();
        StringBuilder head = this.sqlHeadInsert();
        StringBuilder sqlHead = new StringBuilder(head);
        int batchCount = 1000;// 每批commit的个数
        int batchLastIndex = batchCount;// 每批最后一个的下标

        if (list.size() < batchCount) {
            for (int i = 0; i < list.size(); i++) {
                WlLbmbtzlqz tzlqz = list.get(i);
                sql = insertImportBatch(tzlqz);
                if (i != list.size() - 1) {
                    sql.append(" union ");
                }
                sqlForm.append(sql);
            }
            sqlHead.append(sqlForm);
            jdbcDao.executeSql(sqlHead.toString());
            sqlHead = new StringBuilder(head);
            sqlForm = new StringBuilder();
        } else {
            for (int index = 0; index < list.size(); ) {
                List<WlLbmbtzlqz> partRes = list.subList(index, batchLastIndex);
                for (int i = 0; i < partRes.size(); i++) {
                    WlLbmbtzlqz tzlqz = partRes.get(i);
                    sql = insertImportBatch(tzlqz);
                    if (i != partRes.size() - 1) {
                        sql.append(" union ");
                    }
                    sqlForm.append(sql);
                    continue;
                }
                sqlHead.append(sqlForm);
                jdbcDao.executeSql(sqlHead.toString());
                sqlHead = new StringBuilder(head);
                sqlForm = new StringBuilder();
                index = batchLastIndex;// 设置下一批下标
                batchLastIndex = index + (batchCount);//15 20 20  21
                if (batchLastIndex > list.size()) {
                    batchLastIndex = list.size();
                }
            }
        }

    }

    private StringBuilder insertImportBatch(WlLbmbtzlqz tzlqz) {
        StringBuilder sqlValues = new StringBuilder();
        sqlValues.append(" select ");
        sqlValues.append("get_seq('WL_LBMB_TZLQZ_SEQ')").append(",");
        sqlValues.append("'").append(tzlqz.getWl_lbmbtzl_id()).append("',");
        sqlValues.append("'").append(tzlqz.getQz()).append("',");
        sqlValues.append("'").append(tzlqz.getSm()).append("',");
        sqlValues.append("'1',");
        sqlValues.append("'").append(tzlqz.getCreator_id()).append("',");
        sqlValues.append("'").append(tzlqz.getCreator_name()).append("',");
        sqlValues.append("sysdate,");
        sqlValues.append("0");
        sqlValues.append(" from dual");
        return sqlValues;
    }

    private StringBuilder sqlHeadInsert() {
        StringBuilder sqlHeader = new StringBuilder();
        sqlHeader.append("insert into wl_lbmb_tzlqz (id,wl_lbmbtzl_id,qz,sm,status,creator_id,creator_name,create_time,version)");
        return sqlHeader;
    }

    @Override
    public int updateWlLbmbByCond(Map<String, Object> cond) {
        return wlLbmbDao.updateWlLbmbByCond(cond);
    }

    @Override
    public int deleteWlLbmbByCond(Map<String, Object> cond) {
        return wlLbmbDao.deleteWlLbmbByCond(cond);
    }

    @Override
    public List<WlLbmb> getListByDelLbGuanLian(Map<String, Object> cond) {
        return wlLbmbDao.getListByDelLbGuanLian(cond);
    }

    @Override
    public int getCountdelWllbByMbBm(Map<String, Object> cond) {
        return wlLbmbDao.getCountdelWllbByMbBm(cond);
    }

    @Override
    public void exportWlLbMb(List<WlLbmb> list, HttpServletResponse response) {
        try {
            String tablename = "物料模板";
            // 声明一个工作薄
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("物料模板");
            XSSFRow row = sheet.createRow(0);
            XSSFCell cell = null;
            CellStyle style = ExcelUtil.createCellTextStyle(workbook);
            cell = row.createCell(0);
            sheet.setDefaultColumnStyle(0, style);
            cell.setCellValue("模板名称");
            cell = row.createCell(1);
            sheet.setDefaultColumnStyle(1, style);
            cell.setCellValue("所属类别");
            cell = row.createCell(2);
            sheet.setDefaultColumnStyle(2, style);
            cell.setCellValue("模板状态");
            cell = row.createCell(3);
            sheet.setDefaultColumnStyle(3, style);
            cell.setCellValue("创建人");
            cell = row.createCell(4);
            sheet.setDefaultColumnStyle(4, style);
            cell.setCellValue("创建时间");
            cell = row.createCell(5);
            sheet.setDefaultColumnStyle(5, style);
            cell.setCellValue("模板说明");
            for (int i = 0; i < list.size(); i++) {
                WlLbmb lbmb = list.get(i);
                row = sheet.createRow(i + 1);
                cell = row.createCell(0);
                //cell.setCellValue("模板名称");
                cell.setCellValue(lbmb.getMbname());
                cell = row.createCell(1);
                //cell.setCellValue("所属类别");
                cell.setCellValue(lbmb.getLbbm());
                cell = row.createCell(2);
                //cell.setCellValue("模板状态");
                cell.setCellValue(lbmb.getStatus().equals("1") ? "活动" : "冻结");
                cell = row.createCell(3);
                //cell.setCellValue("创建人");
                cell.setCellValue(lbmb.getCreator_name());
                cell = row.createCell(4);
                //cell.setCellValue("创建时间");
                cell.setCellValue(lbmb.getCreate_time());
                cell = row.createCell(5);
                //cell.setCellValue("模板说明");
                cell.setCellValue(lbmb.getRemark());
            }
            ExcelExportUtil.outExportExcel(tablename, workbook, response);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    @Override
    public PageInfo getWlLbmbListByCond2(Map<String, Object> map) {
        PageInfo<List<WlLbmb>> pageInfo = null;
        try {
            Object pNo = map.get("pageNum");
            int pageNum, pageSize;
            // export
            if (pNo != null && !"NaN".equals(pNo)) {
                pageNum = Integer.parseInt(pNo.toString());// 页码
            } else {
                pageNum = 1;
            }
            // pageSize=0时不分页
            pageSize = Integer.parseInt(map.get("limit").toString());// 模型定义页面要显示数据条数
            // 例如
            // 10,20,50,100
            PageHelper.startPage(pageNum, pageSize);// 开始分页标志（当前页，显示条数）
            List<WlLbmb> list = wlLbmbDao.getWlLbmbListByCond(map);
            pageInfo = new PageInfo(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pageInfo;
    }
}
